﻿using Dapper;
using Domain.Entity;
using Domain.Entity.sys;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class BusinessTypeRepository : IBusinessTypeRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加业务类型信息
        public async Task<int> AddBusinessType(BusinessType businessType)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[sys_business_type](bus_name, bus_sort, bus_field, bus_status, create_name, create_time,remark)
                              values('{businessType.bus_name}',{businessType.bus_sort},'{businessType.bus_field}',{businessType.bus_status},'{businessType.create_name}','{businessType.create_time}','{businessType.remark}')";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //删除业务类型信息
        public async Task<int> DeleteBusinessType(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[sys_business_type] set is_delete=1 where bus_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //分页查询业务类型信息
        public async Task<FenYe<BusinessType>> GetBusinessType(FenYe<BusinessType> fenYe, string? typename, int? status, string? field, string? createname)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "bus_id");
                param.Add("@tabelName", "sys_business_type");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(typename))
                    sqlparam += $" and bus_name like '%{typename}%'";
                if (status != null && status != 0)
                    sqlparam += $" and bus_status={status}";
                if (!string.IsNullOrEmpty(field))
                    sqlparam += $" and bus_field='{field}'";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and  create_name like '%{createname}%'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<BusinessType> list = sqlConnection.Query<BusinessType>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据id查询业务类型信息
        public async Task<BusinessType> GetBusinessTypeById(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[sys_business_type] where bus_id={id}";
                return await sqlConnection.QueryFirstAsync<BusinessType>(sql);
            }
        }


        //根据方向查询业务类型
        public async Task<List<BusinessType>> GetBusinessTypes(string field)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[sys_business_type] where bus_field='{field}' and is_delete=0";
                return sqlConnection.Query<BusinessType>(sql).ToList();
            }
        }


        //修改业务类型信息
        public async Task<int> UpdateBusinessType(BusinessType businessType)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[sys_business_type] set bus_name='{businessType.bus_name}',bus_sort='{businessType.bus_sort}',bus_status='{businessType.bus_status}',bus_field='{businessType.bus_field}',update_name='{businessType.update_name}',update_time='{businessType.update_time}',remark='{businessType.remark}' where bus_id={businessType.bus_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
